package moe.dao;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

import moe.config.Constants;
import moe.entity.Collection;
import moe.entity.Post;

public class CollectionDao extends BaseDao {

	public CollectionDao() {
		super(BaseDao.ConnType.COLLECTION);
	}

	public Collection getCollection(final long id) {
		String sql = "SELECT l.id, c.version, path, name FROM collection AS c, library AS l WHERE c.library_id = l.id AND c.id = ?";
		return this.query(sql, new ResultsetHandler<Collection>() {

			public Collection handle(ResultSet rs) throws SQLException {
				Collection result = null;
				if (rs.next()) {
					long lid = rs.getLong("id");
					int version = rs.getInt("version");
					String dir = rs.getString("path");
					String name = rs.getString("name");
					result = new Collection(id, lid, version, dir, name);
				}
				return result;
			}

		}, id);
	}

	public void deleteCollection(long id) {
		String[] sqls = new String[] {
				"DELETE FROM collection WHERE id = " + id,
				"DELETE FROM post WHERE id = " + id
		};
		this.executeBatch(Arrays.asList(sqls));
	}

	public boolean addCollection(final Collection collection) {
		String sql = "SELECT * FROM collection WHERE id = ?";
		int status = this.query(sql, new ResultsetHandler<Integer>() {

			public Integer handle(ResultSet rs) throws SQLException {
				if (rs.next()) {
					if (collection.getVersion() != rs.getInt("version")
							&& (!collection.getName().equals(rs.getString("name"))
							|| collection.getLibraryId() != rs.getLong("library_id"))) {
						return 1;
					}
					return 0;
				}
				return -1;
			}

		}, collection.getId());
		if (status >= 0) {
			if (status == 0) {
				updateCollectionVersion(collection);
			} else {
				updateCollection(collection);
			}
			return false;
		}
		sql = "INSERT INTO collection (id, library_id, name, version) VALUES(?, ?, ?, ?)";
		// sql = AnnotationColumnMapper.mappingToInsert("collection", collection);
		this.update(sql, collection.getId(), collection.getLibraryId(), collection.getName(),
				collection.getVersion());
		return true;
	}

	public List<String> clearResidualCollection(int id) {
		String sql = "DELETE FROM collection WHERE library_id = ? AND version < (SELECT MAX(version) FROM collection)";
		List<String> result = getResidualCollectionName(id);
		this.update(sql, id);
		clearInvalidPost();
		return result;
	}

	private List<String> getResidualCollectionName(int id) {
		String sql = "SELECT name FROM collection WHERE library_id = ? AND version < (SELECT MAX(version) FROM collection)";
		return this.query(sql, new ResultsetHandler<List<String>>() {

			public List<String> handle(ResultSet rs) throws SQLException {
				List<String> result = new LinkedList<String>();
				while (rs.next()) {
					result.add(rs.getString(1));
				}
				return result;
			}

		}, id);
	}

	private void clearInvalidPost() {
		String sql = "DELETE FROM post WHERE id NOT IN (SELECT id FROM collection)";
		this.update(sql);
	}

	private void updateCollection(Collection collection) {
		String sql = "UPDATE collection SET name = ?, library_id = ?, version = ? WHERE id = ?";
		this.update(sql, collection.getName(), collection.getLibraryId(), collection.getVersion(),
				collection.getId());
	}

	private void updateCollectionVersion(Collection collection) {
		String sql = "UPDATE collection SET version = ? WHERE id = ?";
		this.update(sql, collection.getVersion(), collection.getId());
	}

	public Integer[] getMissingPostsId() {
		String sql = "SELECT id FROM collection WHERE id NOT IN (SELECT id FROM post)";
		return this.query(sql, new ResultsetHandler<Integer[]>() {

			public Integer[] handle(ResultSet rs) throws SQLException {
				List<Integer> result = new LinkedList<Integer>();
				while (rs.next()) {
					result.add(rs.getInt(1));
				}
				return result.toArray(new Integer[result.size()]);
			}

		});
	}

	public void addPosts(List<Post> posts) {
		List<String> sqls = new ArrayList<String>(posts.size());
		for (Post post : posts) {
			sqls.add(AnnotationColumnMapper.mappingToInsert("post", post));
		}
		executeBatch(sqls);
	}

	public void createCollectionTables() {
		List<String> sqls = loadSqlStatements(Constants.COLLECTION_TABLE_FILE);
		this.executeBatch(sqls);
	}

	public int getLibraryId(String path) {
		String sql = "SELECT id FROM library WHERE path = ?";
		return this.query(sql, new ResultsetHandler<Integer>() {

			public Integer handle(ResultSet rs) throws SQLException {
				if (rs.next()) {
					return rs.getInt(1);
				}
				return -1;
			}

		}, path);
	}

	public boolean addOrUpdateLibrary(File file, int version) {
		boolean isNeedScan = true;
		int status = getLibraryStatus(file);
		if (status < 0) {// not exists
			addLibrary(file, version);
		} else if (status > 0) {// exists and need update
			updateLibrary(file, version);
		} else {// exists and not need update
			updateLibraryVersion(file, version);
			isNeedScan = false;
		}
		return isNeedScan;
	}

	/*
	 * 
	 * @return lesser than 0	: not exists
	 * 			equals 0		: exists and not need update
	 * 			greater than 0	: exists and need update
	 */
	private int getLibraryStatus(final File file) {
		String sql = "SELECT last_modified FROM library WHERE path = ?";
		return this.query(sql, new ResultsetHandler<Integer>() {

			public Integer handle(ResultSet rs) throws SQLException {
				if (rs.next()) {
					long time = rs.getLong(1);
					if (file.lastModified() != time) {
						return 1;
					}
					return 0;
				}
				return -1;
			}

		}, file.getPath());
	}

	private void addLibrary(File file, int version) {
		String sql = "INSERT INTO library (path, last_modified, version) VALUES (?, ?, ?)";
		this.update(sql, file.getPath(), file.lastModified(), version);
	}

	private void updateLibrary(File file, int version) {
		String sql = "UPDATE library SET last_modified = ?, version = ? WHERE path = ?";
		this.update(sql, file.lastModified(), version, file.getPath());
	}

	private void updateLibraryVersion(File file, int version) {
		String sql = "UPDATE library SET version = ? WHERE path = ?";
		this.update(sql, version, file.getPath());
	}

	public List<String> clearInvalidLibrary(int version) {
		List<String> result = clearInvalidCollectionAndPost(version);
		String sql = "DELETE FROM library WHERE version < ?";
		this.update(sql, version);
		return result;
	}

	private List<String> clearInvalidCollectionAndPost(int version) {
		String sql = "SELECT id FROM library WHERE version < ?";
		Integer[] ids = this.query(sql, new ResultsetHandler<Integer[]>() {

			public Integer[] handle(ResultSet rs) throws SQLException {
				List<Integer> result = new LinkedList<Integer>();
				while (rs.next()) {
					result.add(rs.getInt(1));
				}
				return result.toArray(new Integer[result.size()]);
			}

		}, version);
		List<String> result = new LinkedList<String>();
		for (int id : ids) {
			result.addAll(getInvalidCollectionName(id));
			clearInvalidPost(id);
			clearInvalidCollection(id);
		}
		return result;
	}

	private List<String> getInvalidCollectionName(int id) {
		String sql = "SELECT name FROM collection WHERE library_id = ?";
		return this.query(sql, new ResultsetHandler<List<String>>() {

			public List<String> handle(ResultSet rs) throws SQLException {
				List<String> result = new LinkedList<String>();
				while (rs.next()) {
					result.add(rs.getString(1));
				}
				return result;
			}

		}, id);
	}

	private void clearInvalidCollection(int id) {
		String sql = "DELETE FROM collection WHERE library_id = ?";
		this.update(sql, id);
	}

	private void clearInvalidPost(int id) {
		String sql = "DELETE FROM post WHERE id IN (SELECT id FROM collection WHERE library_id = ?)";
		this.update(sql, id);
	}

	public int getMaxLibraryVersion() {
		String sql = "SELECT MAX(version) FROM library";
		return this.query(sql, new ResultsetHandler<Integer>() {

			public Integer handle(ResultSet rs) throws SQLException {
				int result = 0;
				if (rs.next()) {
					result = rs.getInt(1);
				}
				return result;
			}

		});
	}
}
